/**
 * 
 */
package com.snapp.util.service.db;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Vector;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.snapp.util.Bean;
import com.snapp.util.service.db.mapping.CoreHandlers;
import com.snapp.util.service.db.mapping.TypeHandler;
import com.snapp.util.service.model.ServiceImpl;


/**
 * @author kile
 *
 */
public class DBService extends ServiceImpl implements Serializable {
	private static final long serialVersionUID = 2705485477856421317L;
	
	private static final Logger logger = Logger.getLogger(DBService.class);
	
	/**
	 * 
	 */
	public static HashMap<Class<? extends Object>,Class<? extends TypeHandler>> HANDLER_MAPPINGS = 
			new HashMap<Class<? extends Object>, Class<? extends TypeHandler>>(); 
	
	/**
	 * 
	 * @param clazz
	 * @param handler
	 */
	public static void setHandler(Class<? extends Object> clazz,Class<? extends TypeHandler> handler) {
		HANDLER_MAPPINGS.put(clazz, handler);
	}
	
	/**
	 *  Initialize core TypeHandler classes.
	 */
	public void init() {
		
		for (Class<?> clas : CoreHandlers.class.getClasses()) {
			try {
				@SuppressWarnings("unchecked")
				Class<TypeHandler> clazz = (Class<TypeHandler>)clas;
				
				for (Class<?> c : clazz.newInstance().getClasses()) {
					logger.info("Adding TypeHandler: "+c.getName() + " > " + clazz.getName());
					setHandler(c, clazz);
				}
			} catch(Exception e) {
				logger.info("Could not load handler", e);
			}
		}
	
	}
	
	/**
	 * Get an active database connection for the requested jndi name.
	 * 
	 * @param jndiName
	 * @return Connection
	 */
	public synchronized Connection getConnection(String jndiName) {
		
			Connection dbConn = null;

			try {
				// get initial context
				Context context = new InitialContext();
				
				// lookup the app datasource
				DataSource  ds = (DataSource)context.lookup(jndiName);
				
				// get a connection
				dbConn = ds.getConnection();
			}
			catch (Exception e){
				logger.error("jndi Lookup error on " + jndiName, e);
			}
			
			return dbConn;

	}
	
	/**
	 * Load a disconnected Bean object from the database.
	 * @param jndiName
	 * @param sql
	 * @return
	 */
	public Bean loadBean(String jndiName, String sql) throws SQLException {
		Bean bean = null;
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// get connection and execute query
			conn = getConnection(jndiName);
			st = conn.createStatement();
			// log sql query..
			logger.info("loading bean: "+sql);
			rs = st.executeQuery(sql);
			// get meta data
			ResultSetMetaData rsmd = rs.getMetaData();
			// bump rs for single result...
			if (rs.next()) {
				//create the bean
				bean = new Bean();
				// get columns
				for (int i=1; i<=rsmd.getColumnCount(); i++) {
					bean.setProperty(rsmd.getColumnLabel(i),rs.getObject(i),rs.getString(i));
				}
			}
		} catch (SQLException se) {
			logger.error("Error loading object!",se); 
			//throw new SQLException("");
		} finally {
			try {rs.close();rs = null;}catch(Exception e){}
			try {st.close();st = null;}catch(Exception e){}
			try {conn.close(); conn = null;}catch(Exception e){
				logger.fatal("Cannot close DB connection!", e);
			}
		}
		
		return bean;
	}
	
	/**
	 * Load a disconnected Bean object from the database.
	 * @param jndiName
	 * @param sql
	 * @return
	 */
	public Vector<Bean> listBeans(String jndiName, String sql) throws SQLException {
		Vector<Bean> list = new Vector<Bean>();
		Bean bean = null;
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// get connection and execute query
			conn = getConnection(jndiName);
			st = conn.createStatement();
			logger.info("listing beans: "+sql);
			rs = st.executeQuery(sql);
			// get meta data
			ResultSetMetaData rsmd = rs.getMetaData();
			// bump rs for single result...
			if (rs.next()) {
				//create the bean
				bean = new Bean();
				// get columns
				for (int i=1; i<=rsmd.getColumnCount(); i++) {
					bean.setProperty(rsmd.getColumnLabel(i),rs.getObject(i),rs.getString(i));
				}
				// add first bean with meta data
				list.add(bean);
				// get property names
				Vector<String> pns = bean.getPropertyNames();
				// add the rest
				while (rs.next()) {
					bean = new Bean();
					bean.setPropertyNames(pns);
					for (int i=1; i<=rsmd.getColumnCount(); i++) {
						bean.setProperty(rsmd.getColumnLabel(i),rs.getObject(i),rs.getString(i));
					}
					list.add(bean);	
				}
				
			}
		} catch (SQLException se) {
			logger.error("Error loading object!",se); 
			//throw new SQLException(se);
		} finally {
			try {rs.close();rs = null;}catch(Exception e){}
			try {st.close();st = null;}catch(Exception e){}
			try {conn.close(); conn = null;}catch(Exception e){
				logger.fatal("Cannot close DB connection!", e);
			}
		}
		return list;
	}

	/**
	 * 
	 * @param jndiName - jndi connector name
	 * @param ps - A prepared statement sql string
	 * @param vals - Hashmap of index:Object
	 * @return
	 * @throws SQLException
	 */
	public int execute(String jndiName, String ps, HashMap<Integer,Object> vals) throws SQLException {
		int rv = 0;
		Connection conn = getConnection(jndiName);
		PreparedStatement s = conn.prepareStatement(ps);
		
		Vector<TypeHandler> handlers = new Vector<TypeHandler>();
		
		try {
			Iterator<Integer> itr = vals.keySet().iterator();
			while (itr.hasNext()) {
				Integer key = itr.next();
				Object obj = vals.get(key);
				TypeHandler handler = addToStatement(s, key, obj);
				
				if (handler != null) {
					handlers.add(handler);
				}
			}
			
			s.execute();
			
			s.close();
			conn.close();
			
			for (TypeHandler h : handlers) {
				h.close();
			}
			
		} catch(SQLException se) {
			throw new SQLException(se);
		} finally {
			try {s.close();} catch(Exception e){}
			try {conn.close();} catch(Exception e){}
		}
		
		return rv;
	}

	/**
	 * Test for custom classes to handle them independently 
	 * 
	 * @param s
	 * @param index
	 * @param obj
	 * @throws SQLException
	 */
	private TypeHandler addToStatement(PreparedStatement s, Integer index, Object obj) throws SQLException{
		TypeHandler handler = null;
		
		if (HANDLER_MAPPINGS.containsKey(obj.getClass())) {
			@SuppressWarnings("unchecked")
			Class<TypeHandler> clas = (Class<TypeHandler>)HANDLER_MAPPINGS.get(obj.getClass()); 
			
			try {
			
				handler = clas.newInstance();
				
				handler.process(s, index, obj);
			
			} catch (InstantiationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			logger.info("Processed TypeHandler: "+clas.getName());
			
		} else {
			s.setString((int)index, (String)obj);
		}
		
		return handler;
	}
	
	/**
	 * execute any sql query possibly returning an integer.
	 * @param jndiName
	 * @param sql
	 * @return
	 */
	public int simpleExecute(String jndiName, String sql) throws SQLException {
		int rv = 0;
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// get connection and execute query
			conn = getConnection(jndiName);
			st = conn.createStatement();
			logger.info("executing query: "+sql);
			// try for results
			if (st.execute(sql)) {
				rs = st.getResultSet();
			
				if (rs.next())
					// set return val
					rv = rs.getInt(1);
				
			} 
			
		} catch (SQLException se) {
			logger.error("Error executing query!",se); 
			//throw new SQLException(se);
		} finally {
			try {rs.close();rs = null;}catch(Exception e){}
			try {st.close();st = null;}catch(Exception e){}
			try {conn.close(); conn = null;}catch(Exception e){
				logger.fatal("Cannot close DB connection!", e);
			}
		}

		return rv;
	}

	/**
	 * Fetch Column labels
	 * @param jndiName
	 * @param objectName
	 * @return
	 */
	public Vector<String> getColumnNames(String jndiName, String objectName) throws SQLException {
		Vector<String> columns = new Vector<String>();
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// get connection and execute query
			conn = getConnection(jndiName);
			st = conn.createStatement();
			
			//fetch one row to get metadata
			String sql = "Select * from "+objectName+" limit 1;";
			rs = st.executeQuery(sql);
			
			// get meta data
			ResultSetMetaData rsmd = rs.getMetaData();
			// bump rs for single result...
			for (int i=1; i<=rsmd.getColumnCount(); i++) {
				columns.add(rsmd.getColumnLabel(i));
				logger.info("Adding column label: "+rsmd.getColumnLabel(i));
			}
		
		} catch (SQLException se) {
			logger.error("Error loading object!",se); 
			//throw new SQLException(se);
		} finally {
			try {rs.close();rs = null;}catch(Exception e){}
			try {st.close();st = null;}catch(Exception e){}
			try {conn.close(); conn = null;}catch(Exception e){
				logger.fatal("Cannot close DB connection!", e);
			}
		}
		return columns;
	}

	/**
	 * Fetch Column types
	 * @param jndiName
	 * @param objectName
	 * @return
	 */
	public Vector<String> getColumnTypes(String jndiName, String objectName) throws SQLException {
		Vector<String> types = new Vector<String>();
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// get connection and execute query
			conn = getConnection(jndiName);
			st = conn.createStatement();
			
			//fetch one row to get metadata
			String sql = "Select * from "+objectName+" limit 1;";
			rs = st.executeQuery(sql);
			
			// get meta data
			ResultSetMetaData rsmd = rs.getMetaData();
			// bump rs for single result...
			for (int i=1; i<=rsmd.getColumnCount(); i++) {
				types.add(rsmd.getColumnTypeName(i));
				logger.info("Adding column type: "+rsmd.getColumnTypeName(i));
			}
		
		} catch (SQLException se) {
			logger.error("Error loading object!",se); 
			//throw new SQLException(se);
		} finally {
			try {rs.close();rs = null;}catch(Exception e){}
			try {st.close();st = null;}catch(Exception e){}
			try {conn.close(); conn = null;}catch(Exception e){
				logger.fatal("Cannot close DB connection!", e);
			}
		}
		return types;
	}

	/*******************************************************************
	 * Utility functions...
	 *******************************************************************/
	/*
	 * Clean a String for database input..
	 */
	public String cleanSqlIn(String sql) {
		return sql.replace("'", "''");
	}
}
